package util;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLDecoder;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

public class DBHelper {


    //获取通用的连接对象
    public Connection getCon(){
        Connection con = null;
        FileReader fr = null;
        Properties pro = new Properties();
        String str = DBHelper.class.getClassLoader().getResource("config/jdbc.properties").getPath();

        try {
            String newStr = URLDecoder.decode(str, "utf-8");
            fr = new FileReader(newStr);
            pro.load(fr);
            Class.forName(pro.getProperty("mysql.className"));
            String username = pro.getProperty("mysql.userName");
            String password = pro.getProperty("mysql.passWord");
            String url = pro.getProperty("mysql.url");
            con = DriverManager.getConnection(url, username, password);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return con;
    }

    //通用的关闭

    public void closeAll(Connection con , PreparedStatement ps, ResultSet rs){
        try {
            if (con != null) {
                con.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int update(String sql,Object...obj){
        Connection con = null;
        PreparedStatement ps = null;
        int i = 0 ;
        con = getCon();
        try {
            ps = con.prepareStatement(sql);
            for (int j = 0; j < obj.length; j++) {
                ps.setObject(j + 1, obj[j]);
            }
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(con,ps,null);
        }

        return i;

    }

    //通用的查询

    public ArrayList query(String sql,Class cla,Object...objects){
        ArrayList list = new ArrayList();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        con = getCon();
        try {
            ps = con.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i + 1, objects[i]);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                Object obj = cla.newInstance();
                Field[] declaredFields = cla.getDeclaredFields();
                for (Field f : declaredFields) {
                    f.setAccessible(true);
                    f.set(obj, rs.getObject(f.getName()));
                }
                list.add(obj);
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(con,ps,rs);
        }
        return list;
    }


    //通用的分页查询，返回数据总条数
    public int queryCountData(String sql, Object...objects){
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;

        try {
            con = getCon();
            ps = con.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i + 1, objects[i]);
            }
            rs = ps.executeQuery();

            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(con,ps,rs);
        }
        return count;
    }







}
